package com.boss.salary.dao.impl;

import com.boss.common.util.DateUtils;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.ISalaryMonthEndDao;
import com.boss.salary.entity.AgencyInfo;
import com.boss.salary.entity.AgencyVerify;
import com.boss.salary.entity.SalaryBasedata;
import com.boss.salary.entity.SalaryMonthEnd;
import com.boss.salary.vo.SalaryMonthEndVO;
import com.boss.salary.vo.UnitSalaryReportVO;
import com.boss.sys.utils.SysUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

@Repository
public class SalaryMonthEndDaoImpl extends BaseDaoImpl implements ISalaryMonthEndDao {

    private static DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    private static final int IS_THIRTEEN = 1; //是十三月工资
    private static final int NO_THIRTEEN = 0; //不是十三月工资

    @Override
    public void insertSalaryMonthEndDao(SalaryMonthEnd data) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        LocalDateTime localDateTime = LocalDateTime.now();
        List<Object> params = new ArrayList<Object>() {{
            add(SysUtils.generateGuid());
            add(localDateTime.format(dtf));
            add(localDateTime.format(dtf));
            add(data.getAgencyId());
            add(data.getPersonId());
            add(NO_THIRTEEN);
            add(data.getMofDivCode());
        }};
        StringBuilder sb = new StringBuilder();
        sb.append(" insert into ")
                .append(TableName.SALARY_MONTH_END)
                .append(" (id, create_time, update_time, agency_id, person_id, is_thirteen,mof_div_code ");

        String columnNames = data.getColumnNames();
        if (!StringUtils.isEmpty(columnNames)) {
            String[] columnNameArr = columnNames.split(",");
            List<String> colunmNameList = Arrays.stream(columnNameArr)
                    .map(name -> name.split("_")[0])
                    .collect(Collectors.toList());
            for (String columnName : colunmNameList) {
                sb.append(" , ").append(columnName);
            }
        }
        sb.append(" ) ").append(" values").append("(?,?,?,?,?,?,?");

        if (!StringUtils.isEmpty(columnNames)) {
            String[] columnNameArr = columnNames.split(",");
            List<String> colunmNameList = Arrays.stream(columnNameArr)
                    .map(name -> name.split("_")[0])
                    .collect(Collectors.toList());
            for (String columnName : colunmNameList) {
                sb.append(",?");
                Class<?> clazz = data.getClass();
                Method getMethod = clazz.getDeclaredMethod("get" +
                        columnName.substring(0, 1).toUpperCase() + columnName.substring(1));
                params.add(getMethod.invoke(data));
            }
        }
        sb.append(" ) ");
        super.getjdbcTemplate().update(sb.toString(), params.toArray());
    }

    @Override
    public void updateSalaryMonthEndDao(SalaryMonthEnd data) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        LocalDateTime localDateTime = LocalDateTime.now();
        List<Object> params = new ArrayList<Object>() {{
            add(localDateTime.format(dtf));
            add(data.getAgencyId());
           // add(data.getPersonId());
        }};
        StringBuilder sb = new StringBuilder();
        sb.append(" update ")
                .append(TableName.SALARY_MONTH_END)
                .append(" set ")
                .append(" update_time = ?, agency_id = ? ");
        String columnNames = data.getColumnNames();
        if (!StringUtils.isEmpty(columnNames)) {
            String[] columnNameArr = columnNames.split(",");
            List<String> colunmNameList = Arrays.stream(columnNameArr)
                    .map(name -> name.split("_")[0])
                    .collect(Collectors.toList());
            for (String columnName : colunmNameList) {
                sb.append(" ,").append(columnName).append(" = ? ");
                Class<?> clazz = data.getClass();
                Method getMethod = clazz.getDeclaredMethod("get" +
                        columnName.substring(0, 1).toUpperCase() + columnName.substring(1));
                params.add(getMethod.invoke(data));
            }
        }
        sb.append(" where ");
        if (!StringUtils.isEmpty(data.getId())) {
            sb.append("id = ? ");
            params.add(data.getId());
        } else if (!StringUtils.isEmpty(data.getPersonId())) {
            sb.append(" person_id = ? ");
            params.add(data.getPersonId());
        }
        super.getjdbcTemplate().update(sb.toString(), params.toArray());
    }

    @Override
    public UnitSalaryReportVO selectReallySendSalarySum(String agencyId, Integer queryThirteen, String reallySendSalaryName, String salaryType) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                //如果sum(reallySendSalaryName)为空则把0.00赋值给 sum
                .append(" nvl(sum(").append(reallySendSalaryName).append("), 0.00) reallySendSalarySum, ")
                .append(" nvl(sum(case pio.state when '3' then ").append(reallySendSalaryName)
                .append(" else 0 end ), 0.00) workSalarySum, ")
                .append(" nvl(sum(case pio.state when '1' then ").append(reallySendSalaryName)
                .append(" else 0 end ), 0.00) retireSalarySum ")
                .append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ");
        params.add(agencyId);
        sb.append(" left join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id and smed.agency_id = ? ");
        params.add(agencyId);
        if(SystemDictConstant.IS_FALSE.equals(queryThirteen)){
            sb.append(" and smed.is_thirteen = ? ");
            params.add(queryThirteen);
        }
        sb.append(" where ").append(" pio.is_deleted = 0 ");
        if (!StringUtils.isEmpty(salaryType)) {
            sb.append(" and pet.merit_pay_code = ?");
            params.add(salaryType);
        }
        return super.getjdbcTemplate().queryForObject(sb.toString(), new BeanPropertyRowMapper<>(UnitSalaryReportVO.class), params.toArray());
    }

    @Override
    public UnitSalaryReportVO selectSubmitReallySendSalarySum(String agencyId,String isThirteen ,Integer queryThirteen, String reallySendSalaryName, String salaryType) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                //如果sum(reallySendSalaryName)为空则把0.00赋值给 sum
                .append(" nvl(sum(").append(reallySendSalaryName).append("), 0.00) reallySendSalarySum, ")
                .append(" nvl(sum(case pio.state when '3' then ").append(reallySendSalaryName)
                .append(" else 0 end ), 0.00) workSalarySum, ")
                .append(" nvl(sum(case pio.state when '1' then ").append(reallySendSalaryName)
                .append(" else 0 end ), 0.00) retireSalarySum ")
                .append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ");
        params.add(agencyId);
        sb.append(" left join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id and pet.is_thirteen=smed.is_thirteen and smed.agency_id = ? ");
        params.add(agencyId);
//        if(SystemDictConstant.IS_FALSE.equals(queryThirteen)){
//            sb.append(" and smed.is_thirteen = ? ");
//            params.add(queryThirteen);
//        }
        sb.append(" where ").append(" pio.is_deleted = 0 ").append(" and pet.is_thirteen=? ");
        params.add(isThirteen);
        if (!StringUtils.isEmpty(salaryType)) {
            sb.append(" and pet.merit_pay_code = ?");
            params.add(salaryType);
        }
        return super.getjdbcTemplate().queryForObject(sb.toString(), new BeanPropertyRowMapper<>(UnitSalaryReportVO.class), params.toArray());
    }

    @Override
    public Double selectSubmitReallySendChangeSum(String agencyId, String reallySendSalaryName){
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" nvl(sum(").append(reallySendSalaryName).append("), 0.00) reallySendSalarySum ")
                .append(" from ")
                .append(TableName.SALARY_MONTH_BEGIN)
                .append(" where agency_id = ? ");
        return this.getJdbcTemplate().queryForObject(sql.toString(),Double.class,agencyId);

    }

    @Override
    public String selectSubmitSalarySum(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" sum(").append(queryParam.get("salaryCloumnName")).append(") ")
                .append(" from ")
                .append(TableName.SALARY_MONTH_END).append(" a")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" b")
                .append(" on a.agency_id = b.agency_id")
                .append(" join ").append(TableName.PERSON_INFO).append(" pio")
                .append(" on a.person_id = pio.id")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet")
                .append(" on pio.id = pet.person_id")
                .append(" where ")
                .append(" a.agency_id = '").append(queryParam.get("agencyId")).append("'")
                .append(" and pet.is_stop_salary = 0 ")
                .append(" and b.is_month_end = 0 and b.is_thirteen = 0  and b.salary_verified_in_agency_code = 1");
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                if ("0".equals(queryParam.get("flag"))) {
                    // 未上报和撤销
                    sql.append(" and (b.is_salary_submit = '0' or b.is_salary_submit = '-9')");
                }
            }
            if (!StringUtils.isEmpty(queryParam.get("personType"))) {
                sql.append(" and pio.state = ? ");
                paramList.add(queryParam.get("personType"));
            }
            if (!StringUtils.isEmpty(queryParam.get("personName"))) {
                sql.append(" and pio.person_name like ?");
                paramList.add('%'+queryParam.get("personName").toString()+'%');
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                sql.append(" and pio.iden_no like ? ");
                paramList.add('%'+queryParam.get("idenNo").toString()+'%');
            }
        }
        return super.getjdbcTemplate().queryForObject(sql.toString(), String.class, paramList.toArray());
    }


    @Override
    public List<SalaryMonthEnd> getSalaryMonthEndByAgencyIds(String agencyId,int isThirteen) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from ").append(TableName.SALARY_MONTH_END_SUBMIT)//修改该处查询为上报数据
//                .append(" where ").append(com.boss.sys.utils.SysUtils.inSql("agency_id", agencyIds));
                .append(" where agency_id=? and is_Thirteen=?");

        return this.getjdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryMonthEnd.class),agencyId,isThirteen);
    }

    @Override
    public void updateAgencyIdByPersonId(String personId, String agencyId, String updateTime) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_MONTH_END).append(" set ")
                .append("agency_id = ?, update_time = ?");
        sql.append(" where person_id = ?");
        this.getjdbcTemplate().update(sql.toString(), new Object[]{agencyId, updateTime, personId});
    }

    @Override
    public List<Map<String, Object>> selectPersonSalaryInfoPage(PaginationDTO paginationDTO,
                                                                String agencyId,
                                                                List<String> salaryIds,
                                                                String salaryMonth,
                                                                Map<String, String> currentSalaryIdJoinColumnMap,
                                                                Map<String, Object> queryForm) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append("rownum \"index\", id \"id\",updateTime \"updateTime\",personId \"personId\",agencyId \"agencyId\",currentMonth \"currentMonth\", ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            StringBuilder currentStr = new StringBuilder();
            ifNullCurrentStr = new StringBuilder();
            for (String salaryId : salaryIds) {
                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
                currentStr.append("\"").append(currentColumn).append("\" , ");
                ifNullCurrentStr.append(" nvl( smed.").append(currentColumn).append(", 0 ) ").append(" \"").append(currentColumn).append("\" , ");
            }
            sb.append(currentStr).append("deptId \"deptId\",idenNo \"idenNo\",personName \"personName\" ");
        }
        sb.append(" from ( ");
        sb.append(" select ")
                .append(" smed.id, smed.update_time updateTime, pio.id personId, pet.agency_id agencyId, ")
                .append(" ? currentMonth, ");
        paramList.add(salaryMonth);
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr);
        }
        sb.append(" pet.dept_id deptId, pio.iden_no idenNo, pio.person_name personName ");
        sb.append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" left join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 ")
                .append(" left join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id ")
                .append(" where ")
                .append(" pio.is_deleted = 0 ") // 过滤掉已删除的用户
                .append(" and pet.is_stop_salary = ? ") // 过滤掉已停发的用户
                .append(" and pet.agency_id = ? ");

        paramList.add(SystemDictConstant.IS_FALSE);
        paramList.add(agencyId);
        if(!StringUtils.isEmpty(queryForm.get("salaCardBank"))){
             sb.append(" and pet.sala_card_bank = ?");
             paramList.add(queryForm.get("salaCardBank"));
        }

        sb.append(" ) t order by updateTime desc ");
        String querySql = super.boxUpSql(sb.toString());
        //请求参数初始化
        Object[] params = super.boxUpParam(paramList.toArray(), paginationDTO);
        return super.getJdbcTemplate().queryForList(querySql, params);
    }

    @Override
    public int selectPersonSalaryInfoTotal(String agencyId, Map<String, Object> condition) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" count(*) ")
                .append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" left join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 ")
                .append(" where ")
                .append(" pio.is_deleted = 0 and pet.is_stop_salary = ? and pet.agency_id = ? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        paramList.add(agencyId);
        if(!StringUtils.isEmpty(condition.get("salaCardBank"))){
            sb.append(" and pet.sala_card_bank=?");
            paramList.add(condition.get("salaCardBank"));
        }
        return super.getJdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), int.class);
    }

    @Override
    public List<Map<String, Object>> selectThirteenSalaryPage(String agencyId,
                                                              String salaryMonth,
                                                              List<String> salaryColumns) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select ")
                .append(" \"index\", stmh.id \"id\", ")
                .append(" ? \"salaryMonth\", ");
        paramList.add(salaryMonth);
        sb.append(" pet.dept_id \"deptId\", stmh.agency_id \"agencyId\", pio.id \"personId\", pio.iden_no \"idenNo\", pio.person_name \"personName\" ");
        for (String column : salaryColumns) {
            sb.append(" , nvl(").append(column).append(", 0) ").append(" \"").append(column).append("\" ");
        }
        sb.append(" from ")
                .append(" (select @i:=0) t, ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ")
                .append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" join ")
                .append(TableName.SALARY_THIRTEEN_MONTH).append(" stmh ")
                .append(" on ").append(" stmh.person_id = pio.id and stmh.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" where ").append(" pio.is_deleted = 0 ");
        return super.getjdbcTemplate().queryForList(sb.toString(), paramList.toArray());
    }

    @Override
    public Map<String, Object> selectThirteenSalarySum(String agencyId,
                                                             List<String> salaryColumns) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ");
        for (String column : salaryColumns) {
            sb.append(" nvl(sum(").append(column).append("), 0) \"").append(column).append("\"");
            if(salaryColumns.indexOf(column) != salaryColumns.size() - 1){
                sb.append(" , ");
            }
        }
        sb.append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ")
                .append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ")
                .append(" join ")
                .append(TableName.SALARY_THIRTEEN_MONTH).append(" stmh ")
                .append(" on ").append(" stmh.person_id = pio.id and stmh.is_thirteen = 1 and stmh.agency_id = ? ")
                .append(" where ").append(" pio.is_deleted = 0 ");
        return super.getjdbcTemplate().queryForMap(sb.toString(), agencyId, agencyId);
    }

    @Override
    public void physicalDeleteThirteenMonthSalary(String agencyId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" delete from ")
                .append(TableName.SALARY_THIRTEEN_MONTH)
                .append(" where ")
                .append(" agency_id = ? ");
        super.getjdbcTemplate().update(sb.toString(), agencyId);
    }

    @Override
    public void batchUpdateThirteenMonthSalary(String[] columns,
                                               List<Object[]> list) {
        StringBuilder sb = new StringBuilder();
        sb.append(" update ")
                .append(TableName.SALARY_THIRTEEN_MONTH)
                .append(" set ")
                .append(" update_time = ? ");
        for(String column : columns){
            sb.append(" , ").append(column).append(" = ? ");
        }
        sb.append(" where ").append(" id = ? ");
        this.getJdbcTemplate().batchUpdate(sb.toString(),  new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return list.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = list.get(i);
                for(int a = 0; a < columns.length+2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void addPersonSalary(Object[] params) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.SALARY_MONTH_END);
        sql.append(" (id, create_time, update_time, agency_id, person_id, is_thirteen,mof_div_code) ");
        sql.append(" values (?, ?, ?, ?, ?, ?,?)");
        this.getjdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void deletePersonSalary(String personId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete from ").append(TableName.SALARY_MONTH_END);
        sql.append(" where person_id = ?");
        this.getjdbcTemplate().update(sql.toString(), personId);
    }

    @Override
    public void updatePersonSalary(String personId,String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.SALARY_MONTH_END)
           .append(" set agency_id= ? where person_id = ?");
        Object[] params =new Object[]{
                agencyId,personId
        };
        this.getjdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void updateThirteenSalaryByPersonAndAgencyId(String personId, String agencyId, Double thirteenSalary) {
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sb.append(" update ").append(TableName.SALARY_MONTH_END)
                .append(" set ").append(" update_time = ?, ");
        params.add(String.valueOf(DateUtils.getCurrentTimeStamp()));
        sb.append(" s100 = ?, s1 = s1 + s100, s2 = s2 + s100 ");
        params.add(thirteenSalary);
        sb.append(" where ").append(" person_id = ? and agency_id = ? ");
        params.add(personId);
        params.add(agencyId);
        super.getjdbcTemplate().update(sb.toString(), params.toArray());
    }

    @Override
    public void clearThirteenSalaryByPersonAndAgencyId(String personId, String agencyId) {
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sb.append(" update ").append(TableName.SALARY_MONTH_END)
                .append(" set ").append(" update_time = ?, ");
        params.add(String.valueOf(DateUtils.getCurrentTimeStamp()));
        sb.append(" s1 = s1 - s100, s2 = s2 - s100 ");
        sb.append(" where ").append(" person_id = ? and agency_id = ? ");
        params.add(personId);
        params.add(agencyId);
        super.getjdbcTemplate().update(sb.toString(), params.toArray());
    }

    @Override
    public void clearThirteenSalaryByAgencyId(String agencyId) {
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sb.append(" update ").append(TableName.SALARY_MONTH_END)
                .append(" set ").append(" update_time = ?, ");
        params.add(String.valueOf(DateUtils.getCurrentTimeStamp()));
        sb.append(" s1 = s1 - s100, s2 = s2 - s100, s100 = 0 ");
        sb.append(" where ").append(" agency_id = ? ");
        params.add(agencyId);
        super.getjdbcTemplate().update(sb.toString(), params.toArray());
    }

    @Override
    public int selectCountByPersonAndAgencyId(String personId, String agencyId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select  count(*) from ").append(TableName.SALARY_MONTH_END)
                .append(" where ").append(" agency_id = ? and person_id = ? ");
        return super.getjdbcTemplate().queryForObject(sb.toString(), int.class, agencyId, personId);
    }



    @Override
    public void clearSalaryMonthEndByColum(String colum, AgencyInfo agencyInfo){

        StringBuilder sb = new StringBuilder();
        sb.append(" update ")
                .append(TableName.SALARY_MONTH_END)
                .append(" set ")
                .append(colum)
                .append("=0");

        if(null!=agencyInfo){
            sb.append(" where agency_id=?");
            Object[] params = new Object[]{
                    agencyInfo.getId()
            };
            super.getjdbcTemplate().update(sb.toString(),params);
        }else {
            super.getjdbcTemplate().update(sb.toString());
        }
    }

    @Override
    public void updateRealAndWages(SalaryBasedata salaryBasedata, AgencyInfo agencyInfo) {
        StringBuilder sb = new StringBuilder();
        sb.append(" update ")
                .append(TableName.SALARY_MONTH_END)
                .append(" set s1=s1");
        if(salaryBasedata.getIsAccsalPay().equals(SystemDictConstant.ENABLE)||
                salaryBasedata.getIsAccsalWithhold().equals(SystemDictConstant.ENABLE)){
            sb.append("+")
              .append(salaryBasedata.getSalaryCode()).append(",s2=s2+").append(salaryBasedata.getSalaryCode());
        }else{
            sb.append("-")
                    .append(salaryBasedata.getSalaryCode()).append(",s2=s2-").append(salaryBasedata.getSalaryCode());
        }
        if(null!=agencyInfo){
            sb.append(" where agency_id=?");
            Object[] params = new Object[]{
                    salaryBasedata.getId()
            };
            super.getjdbcTemplate().update(sb.toString(),params);
        }else {
            super.getjdbcTemplate().update(sb.toString());
        }
    }

    @Override
    public void updateSalaryMonthEndByAgencyIdAndPersonId(SalaryMonthEnd data)throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        LocalDateTime localDateTime = LocalDateTime.now();
        List<Object> params = new ArrayList<Object>() {{
            add(localDateTime.format(dtf));
            add(data.getAgencyId());
            add(data.getPersonId());
        }};
        StringBuilder sb = new StringBuilder();
        sb.append(" update ")
                .append(TableName.SALARY_MONTH_END)
                .append(" set ")
                .append(" update_time = ?, agency_id = ?, person_id = ? ");
        String columnNames = data.getColumnNames();
        if (!StringUtils.isEmpty(columnNames)) {
            String[] columnNameArr = columnNames.split(",");
            List<String> colunmNameList = Arrays.stream(columnNameArr)
                    .map(name -> name.split("_")[0])
                    .collect(Collectors.toList());
            for (String columnName : colunmNameList) {
                sb.append(" ,").append(columnName).append(" = ? ");
                Class<?> clazz = data.getClass();
                Method getMethod = clazz.getDeclaredMethod("get" +
                        columnName.substring(0, 1).toUpperCase() + columnName.substring(1));
                params.add(getMethod.invoke(data));
            }
        }
        sb.append(" where ").append("  agency_id= ? and  person_id = ?");
        params.add(data.getAgencyId());
        params.add(data.getPersonId());
        super.getjdbcTemplate().update(sb.toString(), params.toArray());
    }

    @Override
    public void batchUpdateSalaryMonthEnd(List<SalaryMonthEndVO> salaryMonthEndVOs) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        if(!CollectionUtils.isEmpty(salaryMonthEndVOs)){
            Class<?> clazz = SalaryMonthEndVO.class;
            StringBuilder sb = new StringBuilder();
            sb.append(" update ").append(TableName.SALARY_MONTH_END)
                    .append(" set ").append(" update_time = ? ");
            for(int i = 1; i <= 150; i++){
                sb.append(" ,s").append(i).append(" = ? ");
            }
            sb.append(" where ").append(" agency_id = ? and person_id = ? ");
//            super.getjdbcTemplate().batchUpdate(sb.toString(), paramList);
            super.getjdbcTemplate().batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
                public int getBatchSize() {
                    return salaryMonthEndVOs.size();
                    //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
                }
                public void setValues(PreparedStatement ps, int i)throws SQLException {
                    SalaryMonthEndVO sme = salaryMonthEndVOs.get(i);
                    ps.setString(1, sme.getUpdateTime());

                    for(int a = 1; a <= 150; a++){
                        Method getMethod = null;
                        try {
                            getMethod = clazz.getDeclaredMethod("getS"+a);
                            ps.setObject(a+1,getMethod.invoke(sme));
                        } catch (NoSuchMethodException e) {
                            e.printStackTrace();
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        } catch (InvocationTargetException e) {
                            e.printStackTrace();
                        }

                    }
                    ps.setString(152, sme.getAgencyId());
                    ps.setString(153, sme.getPersonId());
                }
            });
        }
    }

    @Override
    public void batchUpdateSalaryMonthEndDouble(List<SalaryMonthEndVO> salaryMonthEndVOs) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        if(!CollectionUtils.isEmpty(salaryMonthEndVOs)){
            Class<?> clazz = SalaryMonthEndVO.class;
            StringBuilder sb = new StringBuilder();
            sb.append(" update ").append(TableName.SALARY_MONTH_END_DOUBLE)
                    .append(" set ").append(" update_time = ? ");
            for(int i = 1; i <= 150; i++){
                sb.append(" ,s").append(i).append(" = ? ");
            }
            sb.append(" where ").append(" agency_id = ? and person_id = ? ");
            super.getjdbcTemplate().batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
                public int getBatchSize() {
                    return salaryMonthEndVOs.size();
                    //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
                }
                public void setValues(PreparedStatement ps, int i)throws SQLException {
                    SalaryMonthEndVO sme = salaryMonthEndVOs.get(i);
                    ps.setString(1, sme.getUpdateTime());

                    for(int a = 1; a <= 150; a++){
                        Method getMethod = null;
                        try {
                            getMethod = clazz.getDeclaredMethod("getS"+a);
                            ps.setObject(a+1,getMethod.invoke(sme));
                        } catch (NoSuchMethodException e) {
                            e.printStackTrace();
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        } catch (InvocationTargetException e) {
                            e.printStackTrace();
                        }

                    }
                    ps.setString(152, sme.getAgencyId());
                    ps.setString(153, sme.getPersonId());
                }
            });
        }
    }

    @Override
    public Integer getPaidWagesCount(String agencyId, String zeroAble) {
        StringBuilder sb = new StringBuilder();
       // String s2="220300000".equals(userDTO.getProvince())?" ssme.s2 < 0 or ssme.s2 is null)":" ssme.s2 <= 0 or ssme.s2 is null)";
        String s2=zeroAble.equals("1")?" nvl(ssme.s2,0) < 0 ":" nvl(ssme.s2,0) <= 0 ";
        sb.append(" SELECT COUNT(*) FROM ").append(TableName.PERSON_INFO)
            .append("  spi left JOIN ").append(TableName.SALARY_MONTH_END)
            .append("  ssme on ssme.person_id = spi.id where ssme.agency_id = ? " )
            .append(" and spi.is_deleted = 0 and (nvl(ssme.s1,0)<0 or ")
            .append(s2)
            .append(" )");
        return super.getjdbcTemplate().queryForObject(sb.toString(), int.class, agencyId);
    }
    @Override
    public Integer getPaidWagesCountDouble(String agencyId, String zeroAbles) {
        StringBuilder sb = new StringBuilder();
        // String s2="220300000".equals(userDTO.getProvince())?" ssme.s2 < 0 or ssme.s2 is null)":" ssme.s2 <= 0 or ssme.s2 is null)";
        String s2=zeroAbles.equals("1")?" nvl(ssme.s2,0) < 0 ":" nvl(ssme.s2,0) <= 0 ";
        sb.append(" SELECT COUNT(*) FROM ").append(TableName.PERSON_INFO)
                .append("  spi left JOIN ").append(TableName.SALARY_MONTH_END_DOUBLE)
                .append("  ssme on ssme.person_id = spi.id where ssme.agency_id = ? " )
                .append(" and spi.is_deleted = 0 and (nvl(ssme.s1,0)<0 or ")
                .append(s2)
                .append(" )");
        return super.getjdbcTemplate().queryForObject(sb.toString(), int.class, agencyId);
    }

    @Override
    public List<String> selectDifferentPersonId(String agencyId) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select id from ( ")
                .append(" select ").append(" pio.id ");
        StringBuilder whereStr = new StringBuilder(" s2 = 0 ");
        for(int i = 1; i <= 150; i++){
            sb.append(" ,nvl(smed.s").append(i).append(", 0) s").append(i);
            sb.append(" ,nvl(smbn.s").append(i).append(", 0) s").append(i).append("_history ");
            whereStr.append(" or s").append(i).append(" != ").append("s").append(i).append("_history ");
        }
        sb.append(" from ").append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 and pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append("  join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id and smed.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" left  join ").append(TableName.SALARY_MONTH_BEGIN).append(" smbn ")
                .append(" on ").append(" pio.id = smbn.person_id and smbn.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" where ").append(" pio.is_deleted = 0 ");
        sb.append(" ) t ").append(" where ").append(whereStr.toString());
        return super.getjdbcTemplate().queryForList(sb.toString(), paramList.toArray(), String.class);
    }

    @Override
    public void updateRealAndWagesAndSub(String agencyId,List<String> addlist,List<String> sublist) {
        StringBuilder sb = new StringBuilder();
        sb.append(" update ")
                .append(TableName.SALARY_MONTH_END)
                .append(" set s1=");
        StringBuilder addStr= new StringBuilder();
        StringBuilder subStr= new StringBuilder();
        addStr.append("0");
        for(String add:addlist){
            addStr.append("+")
                    .append("nvl(")
                    .append(add)
                    .append(",0)");
        }
        subStr.append("0");
        for(String sub:sublist){
            subStr.append("-")
                    .append("nvl(")
                    .append(sub)
                    .append(",0)");
        }
        sb.append(addStr)
                .append("-")
                .append(subStr);
        sb.append(",s2=")
                .append(addStr);

        if(null!=agencyId){
            sb.append(" where agency_id=?");
            Object[] params = new Object[]{
                    agencyId
            };
            super.getjdbcTemplate().update(sb.toString(),params);
        }else {
            super.getjdbcTemplate().update(sb.toString());
        }
    }

    @Override
    public List<SalaryMonthEndVO> selectSalaryMonthEndWithSalaryType(String province, List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select ")
                .append(" pet.agency_id, pet.person_id, pet.merit_pay_code, pet.mof_div_code,is_thirteen, ")
                .append(" s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, ")
                .append(" s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, s39, s40, ")
                .append(" s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, s57, s58, s59, s60, ")
                .append(" s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, s75, s76, s77, s78, s79, s80, ")
                .append(" s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, s93, s94, s95, s96, s97, s98, s99, s100, ")
                .append(" s101,s102,s103,s104,s105,s106,s107,s108,s109,s110,s111,s112,s113,s114,s115,s116,s117,s118,s119,s120, ")
                .append(" s121,s122,s123,s124,s125,s126,s127,s128,s129,s130,s131,s132,s133,s134,s135,s136,s137,s138,s139,s140, ")
                .append(" s141,s142,s143,s144,s145,s146,s147,s148,s149,s150 ")
                .append(" from ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id ")
                .append(" where ").append(" pet.is_deleted = 0  and pet.is_stop_salary=0 and smed.mof_div_code = ?  ");
        paramList.add(province);
        if(!CollectionUtils.isEmpty(agencyIds)){
            sb.append(" and ").append(SysUtils.inSql("smed.agency_id", agencyIds));
        }
        try{
            return super.getJdbcTemplate().query(sb.toString(), paramList.toArray(), new BeanPropertyRowMapper<>(SalaryMonthEndVO.class));
        }catch(EmptyResultDataAccessException e){
            return Collections.emptyList();
        }
    }
    @Override
    public List<SalaryMonthEndVO> selectSalaryMonthEndWithSalaryTypes(String province, String agencyIds) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select ")
                .append(" pet.agency_id, pet.person_id, pet.merit_pay_code, pet.mof_div_code,is_thirteen, ")
                .append(" s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, ")
                .append(" s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, s39, s40, ")
                .append(" s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, s57, s58, s59, s60, ")
                .append(" s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, s75, s76, s77, s78, s79, s80, ")
                .append(" s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, s93, s94, s95, s96, s97, s98, s99, s100, ")
                .append(" s101,s102,s103,s104,s105,s106,s107,s108,s109,s110,s111,s112,s113,s114,s115,s116,s117,s118,s119,s120, ")
                .append(" s121,s122,s123,s124,s125,s126,s127,s128,s129,s130,s131,s132,s133,s134,s135,s136,s137,s138,s139,s140, ")
                .append(" s141,s142,s143,s144,s145,s146,s147,s148,s149,s150 ")
                .append(" from ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id ")
                .append(" where ").append(" pet.is_deleted = 0 and smed.mof_div_code = ? ");
        paramList.add(province);
        if(!"null".equals(agencyIds)&&null!=agencyIds){
            sb.append(" and  smed.agency_id = ?");
            paramList.add(agencyIds);
        }
        try{
            return super.getJdbcTemplate().query(sb.toString(), paramList.toArray(), new BeanPropertyRowMapper<>(SalaryMonthEndVO.class));
        }catch(EmptyResultDataAccessException e){
            return  Collections.emptyList();
        }
    }

    @Override
    public List<SalaryMonthEnd> getSalaryMonthEndByAgencyId(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select AGENCY_ID,PERSON_ID,IS_THIRTEEN,MOF_DIV_CODE from ").append(TableName.SALARY_MONTH_END)//修改该处查询为上报数据
//                .append(" where ").append(com.boss.sys.utils.SysUtils.inSql("agency_id", agencyIds));
                .append(" where agency_id=? ");
        return this.getjdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryMonthEnd.class),agencyId);
    }

    @Override
    public SalaryMonthEnd getAgencyVerifyIsThirteen(String agencyId,String province) {
        StringBuilder sql = new StringBuilder();
            sql.append(" select agency_id,is_thirteen,agency_name from ").append(TableName.AGENCY_VERIFY)//修改该处查询为上报数据
//                .append(" where ").append(com.boss.sys.utils.SysUtils.inSql("agency_id", agencyIds));
                .append(" where is_month_end and  agency_id=? and MOF_DIV_CODE = ? ");
        return this.getjdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(SalaryMonthEnd.class),agencyId,province);
    }



    @Override
    public List<SalaryMonthEndVO> selectSalaryMonthEndSubmitWithSalaryType(String province, List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select ")
                .append(" pet.agency_id, pet.person_id, pet.merit_pay_code, pet.mof_div_code, ")
                .append(" s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, ")
                .append(" s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, s39, s40, ")
                .append(" s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, s57, s58, s59, s60, ")
                .append(" s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, s75, s76, s77, s78, s79, s80, ")
                .append(" s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, s93, s94, s95, s96, s97, s98, s99, s100, ")
                .append(" s101,s102,s103,s104,s105,s106,s107,s108,s109,s110,s111,s112,s113,s114,s115,s116,s117,s118,s119,s120, ")
                .append(" s121,s122,s123,s124,s125,s126,s127,s128,s129,s130,s131,s132,s133,s134,s135,s136,s137,s138,s139,s140, ")
                .append(" s141,s142,s143,s144,s145,s146,s147,s148,s149,s150 ")
                .append(" from ").append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id ")
                .append(" where ").append(" pet.is_deleted = 0 and smed.mof_div_code = ? ");
        paramList.add(province);
        if(!CollectionUtils.isEmpty(agencyIds)){
            sb.append(" and ").append(SysUtils.inSql("smed.agency_id", agencyIds));
        }
        try{
            return super.getJdbcTemplate().query(sb.toString(), paramList.toArray(), new BeanPropertyRowMapper<>(SalaryMonthEndVO.class));
        }catch(EmptyResultDataAccessException e){
            return Collections.emptyList();
        }
    }
    @Override
    public List<SalaryMonthEndVO> selectSalaryMonthEndSubmitWithSalaryTypes(String province,String agencyId) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select ")
                .append(" pet.agency_id, pet.person_id, pet.merit_pay_code, pet.mof_div_code,pet.is_thirteen ")
                .append(" s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, ")
                .append(" s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, s39, s40, ")
                .append(" s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, s57, s58, s59, s60, ")
                .append(" s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, s75, s76, s77, s78, s79, s80, ")
                .append(" s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, s93, s94, s95, s96, s97, s98, s99, s100, ")
                .append(" s101,s102,s103,s104,s105,s106,s107,s108,s109,s110,s111,s112,s113,s114,s115,s116,s117,s118,s119,s120, ")
                .append(" s121,s122,s123,s124,s125,s126,s127,s128,s129,s130,s131,s132,s133,s134,s135,s136,s137,s138,s139,s140, ")
                .append(" s141,s142,s143,s144,s145,s146,s147,s148,s149,s150 ")
                .append(" from ").append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id  and pet.is_thirteen = smed.is_thirteen ")
                .append(" where ").append(" pet.is_deleted = 0 and smed.mof_div_code = ? and smed.is_thirteen=0 ");
        paramList.add(province);
        if (null != agencyId && !"null".equals(agencyId)) {
            sb.append(" and smed.agency_id=?");
            paramList.add(agencyId);
        }
        try {
            return super.getJdbcTemplate().query(sb.toString(), new BeanPropertyRowMapper<>(SalaryMonthEndVO.class), paramList.toArray());
        } catch (EmptyResultDataAccessException e) {
            return  Collections.emptyList();
        }
    }


            @Override
    public List<SalaryMonthEndVO> querySalaryByPersonIdOrMofDivCode(String mofDivCode, List<String> personIds) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select ")
                .append(" pet.agency_id, pet.person_id, pet.merit_pay_code, pet.mof_div_code, ")
                .append(" s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, ")
                .append(" s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, s39, s40, ")
                .append(" s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, s57, s58, s59, s60, ")
                .append(" s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, s75, s76, s77, s78, s79, s80, ")
                .append(" s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, s93, s94, s95, s96, s97, s98, s99, s100, ")
                .append(" s101,s102,s103,s104,s105,s106,s107,s108,s109,s110,s111,s112,s113,s114,s115,s116,s117,s118,s119,s120, ")
                .append(" s121,s122,s123,s124,s125,s126,s127,s128,s129,s130,s131,s132,s133,s134,s135,s136,s137,s138,s139,s140, ")
                .append(" s141,s142,s143,s144,s145,s146,s147,s148,s149,s150 ")
                .append(" from ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id ")
                .append(" where ").append(" pet.is_deleted = 0 and smed.mof_div_code = ? ");
        paramList.add(mofDivCode);
        if(!CollectionUtils.isEmpty(personIds)){
            sb.append(" and ").append(SysUtils.inSql("smed.person_id", personIds));
        }
        try{
            return super.getJdbcTemplate().query(sb.toString(), paramList.toArray(), new BeanPropertyRowMapper<>(SalaryMonthEndVO.class));
        }catch(EmptyResultDataAccessException e){
            return Collections.emptyList();
        }
    }

    @Override
    public List<Map<String, Object>> selectMXOffer(String mof_div_code, List<String> columnList) {
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sb.append(" select ")
                .append(" pet.sala_card_bank \"salaCardBank\", ")
                .append(" avy.agency_code \"agencyCode\", ")
                .append(" avy.agency_name \"agencyName\", ")
                .append(" case when pet.state = '3' then '在职'else '离退休' end \"state\", ")
                .append(" case when pet.merit_pay_code is null or pet.merit_pay_code = '' then '0' else pet.merit_pay_code end  \"meritPayCode\", ")
                .append(" case when pet.sala_gr_code is null or pet.sala_gr_code = '' then '0' else sala_gr_code end  \"salaGrCode\", ")
                .append(" decode(smed.is_thirteen,1,'2021-13',avy.salary_month)||pet.iden_no \"personCode\", ")
                .append(" pet.sala_card_no \"salaCardNo\", ")
                .append(" pet.person_name \"personName\", ")
                .append(" pet.iden_no \"idenNo\" ");
        if(!CollectionUtils.isEmpty(columnList)){
            for(String column : columnList){
                if(!"0".equals(column)){
                    sb.append(" , case when smed.").append(column).append(" is null ")
                            .append(" or smed.").append(column).append(" = 0 then  0  else smed.").append(column)
                            .append(" end \"").append(column).append("\" ");
                }
            }
        }
        sb.append(" from ").append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
                .append(" on ").append(" smed.person_id = pet.person_id and smed.agency_id=pet.agency_id ")
                .append(" and pet.is_thirteen=smed.is_thirteen ")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" avy ")
                .append(" on ").append(" avy.agency_id = pet.agency_id and avy.is_deleted = 0 ")
                .append(" and pet.is_thirteen=avy.is_thirteen ")
                .append(" and avy.is_bank_data_created = 0 and avy.is_month_end = 0 and is_pay_data_created = 1")
                .append(" where pet.mof_div_code=? ");
        params.add(mof_div_code);
        return super.getjdbcTemplate().queryForList(sb.toString(), params.toArray());
    }

    /**
     * 通过点位id 查询这个单位下的所有人的工资项
     * @param agencyId
     * @return
     */
    @Override
    public List<SalaryMonthEndVO> getSalaryMonthEndVOByPersonId(String agencyId) {
        StringBuilder sb = new StringBuilder();
        sb.append("select  a.create_time,a.update_time,a.agency_id,a.person_id,a.mof_div_code,b.merit_pay_code,b.iden_no,")
                .append("a.s1, a.s2, a.s3, a.s4, a.s5, a.s6, a.s7, a.s8, a.s9, a.s10, a.s11, a.s12, a.s13, a.s14, a.s15, a.s16, a.s17, a.s18, a.s19, a.s20, ")
                .append("a.s21, a.s22, a.s23, a.s24, a.s25, a.s26, a.s27, a.s28, a.s29, a.s30, a.s31, a.s32, a.s33, a.s34, a.s35, a.s36, a.s37, a.s38, a.s39, a.s40, ")
                .append("a.s41, a.s42, a.s43, a.s44, a.s45, a.s46, a.s47, a.s48, a.s49, a.s50, a.s51, a.s52, a.s53, a.s54, a.s55, a.s56, a.s57, a.s58, a.s59, a.s60, ")
                .append("a.s61, a.s62, a.s63, a.s64, a.s65, a.s66, a.s67, a.s68, a.s69, a.s70, a.s71, a.s72, a.s73, a.s74, a.s75, a.s76, a.s77, a.s78, a.s79, a.s80, ")
                .append("a.s81, a.s82, a.s83, a.s84, a.s85, a.s86, a.s87, a.s88, a.s89, a.s90, a.s91, a.s92, a.s93, a.s94, a.s95, a.s96, a.s97, a.s98, a.s99, a.s100,")
                .append("a.s101,a.s102,a.s103,a.s104,a.s105,a.s106,a.s107,a.s108,a.s109,a.s110,a.s111,a.s112,a.s113,a.s114,a.s115,a.s116,a.s117,a.s118,a.s119,a.s120,")
                .append("a.s121,a.s122,a.s123,a.s124,a.s125,a.s126,a.s127,a.s128,a.s129,a.s130,a.s131,a.s132,a.s133,a.s134,a.s135,a.s136,a.s137,a.s138,a.s139,a.s140,")
                .append("a.s141,a.s142,a.s143,a.s144,a.s145,a.s146,a.s147,a.s148,a.s149,a.s150 from ")
                .append(TableName.SALARY_MONTH_END).append(" a ")
                .append(" join ").append(TableName.PERSON_EXT).append(" b ")
                .append(" on a.person_id=b.person_id ")
                .append(" where ")
                .append("a.agency_id=?");
        Object[] params= new Object[]{
                agencyId
        };
        return this.getjdbcTemplate().query(sb.toString(),new BeanPropertyRowMapper(SalaryMonthEndVO.class),params);
    }

    @Override
    public List<SalaryMonthEndVO> getSalaryMonthEndVOByPersonIdDouble(String agencyId) {
        StringBuilder sb = new StringBuilder();
        sb.append("select  a.create_time,a.update_time,a.agency_id,a.person_id,a.mof_div_code,b.merit_pay_code,b.iden_no,")
                .append("a.s1, a.s2, a.s3, a.s4, a.s5, a.s6, a.s7, a.s8, a.s9, a.s10, a.s11, a.s12, a.s13, a.s14, a.s15, a.s16, a.s17, a.s18, a.s19, a.s20, ")
                .append("a.s21, a.s22, a.s23, a.s24, a.s25, a.s26, a.s27, a.s28, a.s29, a.s30, a.s31, a.s32, a.s33, a.s34, a.s35, a.s36, a.s37, a.s38, a.s39, a.s40, ")
                .append("a.s41, a.s42, a.s43, a.s44, a.s45, a.s46, a.s47, a.s48, a.s49, a.s50, a.s51, a.s52, a.s53, a.s54, a.s55, a.s56, a.s57, a.s58, a.s59, a.s60, ")
                .append("a.s61, a.s62, a.s63, a.s64, a.s65, a.s66, a.s67, a.s68, a.s69, a.s70, a.s71, a.s72, a.s73, a.s74, a.s75, a.s76, a.s77, a.s78, a.s79, a.s80, ")
                .append("a.s81, a.s82, a.s83, a.s84, a.s85, a.s86, a.s87, a.s88, a.s89, a.s90, a.s91, a.s92, a.s93, a.s94, a.s95, a.s96, a.s97, a.s98, a.s99, a.s100,")
                .append("a.s101,a.s102,a.s103,a.s104,a.s105,a.s106,a.s107,a.s108,a.s109,a.s110,a.s111,a.s112,a.s113,a.s114,a.s115,a.s116,a.s117,a.s118,a.s119,a.s120,")
                .append("a.s121,a.s122,a.s123,a.s124,a.s125,a.s126,a.s127,a.s128,a.s129,a.s130,a.s131,a.s132,a.s133,a.s134,a.s135,a.s136,a.s137,a.s138,a.s139,a.s140,")
                .append("a.s141,a.s142,a.s143,a.s144,a.s145,a.s146,a.s147,a.s148,a.s149,a.s150 from ")
                .append(TableName.SALARY_MONTH_END_DOUBLE).append(" a ")
                .append(" join ").append(TableName.PERSON_EXT).append(" b ")
                .append(" on a.person_id=b.person_id ")
                .append(" where ")
                .append("a.agency_id=?");
        Object[] params= new Object[]{
                agencyId
        };
        return this.getjdbcTemplate().query(sb.toString(),new BeanPropertyRowMapper(SalaryMonthEndVO.class),params);
    }

    @Override
    public void batchSaveSalaryMonthEnd(List<SalaryMonthEndVO> salaryMonthEndVOList) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        List<Object[]> paramList = new ArrayList<>(salaryMonthEndVOList.size()-1);
        Class<?> clazz = SalaryMonthEndVO.class;
        for(SalaryMonthEndVO salaryMonthEndVO : salaryMonthEndVOList){
            Object[] paramArr = {
                    SysUtils.generateGuid(), String.valueOf(DateUtils.getCurrentTimeStamp()), String.valueOf(DateUtils.getCurrentTimeStamp()),
                    salaryMonthEndVO.getAgencyId(), salaryMonthEndVO.getPersonId(), salaryMonthEndVO.getIsThirteen(),salaryMonthEndVO.getMofDivCode()
            };
            List<Object> params = new ArrayList<>(Arrays.asList(paramArr));
            for(int i = 1; i <= 150; i++){
                Method getMethod = clazz.getDeclaredMethod("getS"+i);
                params.add(getMethod.invoke(salaryMonthEndVO));
            }
            paramList.add(params.toArray());
        }
        StringBuilder sb = new StringBuilder();
        sb.append(" merge into ").append(TableName.SALARY_MONTH_END)
                .append(" a using " )
                .append(" (select ? id, ? create_time, ? update_time, ? agency_id, ? person_id, ? is_thirteen,? mof_div_code, ? ")
                .append(" s1, ? s2, ? s3, ? s4, ? s5, ? s6, ? s7, ? s8, ? s9, ? s10, ? s11, ? s12, ? s13, ? s14, ? s15, ? s16, ? s17, ? s18, ? s19, ? s20, ?  ")
                .append(" s21, ? s22, ? s23, ? s24, ? s25, ? s26, ? s27, ? s28, ? s29, ? s30, ? s31, ? s32, ? s33, ? s34, ? s35, ? s36, ? s37, ? s38, ? s39, ? s40, ? ")
                .append(" s41, ? s42, ? s43, ? s44, ? s45, ? s46, ? s47, ? s48, ? s49, ? s50, ? s51, ? s52, ? s53, ? s54, ? s55, ? s56, ? s57, ? s58, ? s59, ? s60, ? ")
                .append(" s61, ? s62, ? s63, ? s64, ? s65, ? s66, ? s67, ? s68, ? s69, ? s70, ? s71, ? s72, ? s73, ? s74, ? s75, ? s76, ? s77, ? s78, ? s79, ? s80, ? ")
                .append(" s81, ? s82, ? s83, ? s84, ? s85, ? s86, ? s87, ? s88, ? s89, ? s90, ? s91, ? s92, ? s93, ? s94, ? s95, ? s96, ? s97, ? s98, ? s99, ? s100, ? ")
                .append(" s101, ? s102, ? s103, ? s104, ? s105, ? s106, ? s107, ? s108, ? s109, ? s110, ? s111, ? s112, ? s113, ? s114, ? s115, ? s116, ? s117, ? s118, ? s119, ? s120, ? ")
                .append(" s121, ? s122, ? s123, ? s124, ? s125, ? s126, ? s127, ? s128, ? s129, ? s130, ? s131, ? s132, ? s133, ? s134, ? s135, ? s136, ? s137, ? s138, ? s139, ? s140, ? ")
                .append(" s141, ? s142, ? s143, ? s144, ? s145, ? s146, ? s147, ? s148, ? s149, ? s150 from dual ) b ")
                .append(" on (a.person_id = b.person_id) " )
                .append(" WHEN MATCHED THEN " )
                .append("  UPDATE SET  a.create_time=b.create_time, a.update_time=b.update_time, a.agency_id=b.agency_id, a.is_thirteen=b.is_thirteen, a.mof_div_code=b.mof_div_code, " )
                .append(" a.s1=b.s1, a.s2=b.s2, a.s3=b.s3,a.s4=b.s4,a.s5=b.s5,a.s6=b.s6, a.s7=b.s7,a.s8=b.s8, a.s9=b.s9,a.s10=b.s10, a.s11=b.s11, a.s12=b.s12,a.s13=b.s13, a.s14=b.s14,a.s15=b.s15, a.s16=b.s16, a.s17=b.s17,a.s18=b.s18, a.s19=b.s19,a.s20=b.s20, ")
                .append(" a.s21=b.s21, a.s22=b.s22, a.s23=b.s23,a.s24=b.s24, a.s25=b.s25,a.s26=b.s26, a.s27=b.s27, a.s28=b.s28,a.s29=b.s29, a.s30=b.s30,a.s31=b.s31, a.s32=b.s32, a.s33=b.s33,a.s34=b.s34, a.s35=b.s35,a.s36=b.s36, a.s37=b.s37, a.s38=b.s38,a.s39=b.s39, a.s40=b.s40, ")
                .append(" a.s41=b.s41, a.s42=b.s42, a.s43=b.s43,a.s44=b.s44, a.s45=b.s45,a.s46=b.s46, a.s47=b.s47, a.s48=b.s48,a.s49=b.s49, a.s50=b.s50,a.s51=b.s51, a.s52=b.s52, a.s53=b.s53,a.s54=b.s54, a.s55=b.s55,a.s56=b.s56, a.s57=b.s57, a.s58=b.s58,a.s59=b.s59, a.s60=b.s60,  ")
                .append(" a.s61=b.s61, a.s62=b.s62, a.s63=b.s63,a.s64=b.s64, a.s65=b.s65,a.s66=b.s66, a.s67=b.s67, a.s68=b.s68,a.s69=b.s69, a.s70=b.s70,a.s71=b.s71, a.s72=b.s72, a.s73=b.s73,a.s74=b.s74, a.s75=b.s75,a.s76=b.s76, a.s77=b.s77, a.s78=b.s78,a.s79=b.s79, a.s80=b.s80,  ")
                .append(" a.s81=b.s81, a.s82=b.s82, a.s83=b.s83,a.s84=b.s84, a.s85=b.s85,a.s86=b.s86, a.s87=b.s87, a.s88=b.s88,a.s89=b.s89, a.s90=b.s90,a.s91=b.s91, a.s92=b.s92, a.s93=b.s93,a.s94=b.s94, a.s95=b.s95,a.s96=b.s96, a.s97=b.s97, a.s98=b.s98,a.s99=b.s99, a.s100=b.s100,  ")
                .append(" a.s101=b.s101, a.s102=b.s102, a.s103=b.s103,a.s104=b.s104, a.s105=b.s105,a.s106=b.s106, a.s107=b.s107, a.s108=b.s108,a.s109=b.s109, a.s110=b.s110,a.s111=b.s111, a.s112=b.s112, a.s113=b.s113,a.s114=b.s114,a.s115=b.s115, a.s116=b.s116,a.s117=b.s117,a.s118=b.s118, a.s119=b.s119, a.s120=b.s120,  ")
                .append(" a.s121=b.s121, a.s122=b.s122, a.s123=b.s123,a.s124=b.s124, a.s125=b.s125,a.s126=b.s126, a.s127=b.s127, a.s128=b.s128,a.s129=b.s129, a.s130=b.s130,a.s131=b.s131, a.s132=b.s132, a.s133=b.s133,a.s134=b.s134, a.s135=b.s135,a.s136=b.s136, a.s137=b.s137, a.s138=b.s138,a.s139=b.s139, a.s140=b.s140,  ")
                .append(" a.s141=b.s141, a.s142=b.s142,a.s143=b.s143, a.s144=b.s144,a.s145=b.s145, a.s146=b.s146, a.s147=b.s147,a.s148=b.s148, a.s149=b.s149,a.s150=b.s150 ")
                .append( " WHEN NOT MATCHED THEN " )
                .append( "   INSERT " )
                .append(" ( a.id, a.create_time, a.update_time, a.agency_id, a.person_id, a.is_thirteen,a.mof_div_code, ")
                .append(" a.s1, a.s2, a.s3, a.s4, a.s5, a.s6, a.s7, a.s8, a.s9, a.s10, a.s11, a.s12, a.s13, a.s14, a.s15, a.s16, a.s17, a.s18, a.s19, a.s20,  ")
                .append(" a.s21, a.s22, a.s23, a.s24, a.s25, a.s26, a.s27, a.s28, a.s29, a.s30, a.s31, a.s32, a.s33, a.s34, a.s35, a.s36, a.s37, a.s38, a.s39, a.s40, ")
                .append(" a.s41, a.s42, a.s43, a.s44, a.s45, a.s46, a.s47, a.s48, a.s49, a.s50, a.s51, a.s52, a.s53, a.s54, a.s55, a.s56, a.s57, a.s58, a.s59, a.s60, ")
                .append(" a.s61, a.s62, a.s63, a.s64, a.s65, a.s66, a.s67, a.s68, a.s69, a.s70, a.s71, a.s72, a.s73, a.s74, a.s75, a.s76, a.s77, a.s78, a.s79, a.s80, ")
                .append(" a.s81, a.s82, a.s83, a.s84, a.s85, a.s86, a.s87, a.s88, a.s89, a.s90, a.s91, a.s92, a.s93, a.s94, a.s95, a.s96, a.s97, a.s98, a.s99, a.s100, ")
                .append(" a.s101,a.s102,a.s103,a.s104,a.s105,a.s106,a.s107,a.s108,a.s109,a.s110,a.s111,a.s112,a.s113,a.s114,a.s115,a.s116,a.s117,a.s118,a.s119,a.s120, ")
                .append(" a.s121,a.s122,a.s123,a.s124,a.s125,a.s126,a.s127,a.s128,a.s129,a.s130,a.s131,a.s132,a.s133,a.s134,a.s135,a.s136,a.s137,a.s138,a.s139,a.s140, ")
                .append(" a.s141,a.s142,a.s143,a.s144,a.s145,a.s146,a.s147,a.s148,a.s149,a.s150 ) ")
                .append( "   VALUES " )
                .append(" ( b.id, b.create_time, b.update_time, b.agency_id, b.person_id, b.is_thirteen,b.mof_div_code, ")
                .append(" b.s1, b.s2, b.s3, b.s4, b.s5, b.s6, b.s7, b.s8, b.s9, b.s10, b.s11, b.s12, b.s13, b.s14, b.s15, b.s16, b.s17, b.s18, b.s19, b.s20,  ")
                .append(" b.s21, b.s22, b.s23, b.s24, b.s25, b.s26, b.s27, b.s28, b.s29, b.s30, b.s31, b.s32, b.s33, b.s34, b.s35, b.s36, b.s37, b.s38, b.s39, b.s40, ")
                .append(" b.s41, b.s42, b.s43, b.s44, b.s45, b.s46, b.s47, b.s48, b.s49, b.s50, b.s51, b.s52, b.s53, b.s54, b.s55, b.s56, b.s57, b.s58, b.s59, b.s60, ")
                .append(" b.s61, b.s62, b.s63, b.s64, b.s65, b.s66, b.s67, b.s68, b.s69, b.s70, b.s71, b.s72, b.s73, b.s74, b.s75, b.s76, b.s77, b.s78, b.s79, b.s80, ")
                .append(" b.s81, b.s82, b.s83, b.s84, b.s85, b.s86, b.s87, b.s88, b.s89, b.s90, b.s91, b.s92, b.s93, b.s94, b.s95, b.s96, b.s97, b.s98, b.s99, b.s100, ")
                .append(" b.s101,b.s102,b.s103,b.s104,b.s105,b.s106,b.s107,b.s108,b.s109,b.s110,b.s111,b.s112,b.s113,b.s114,b.s115,b.s116,b.s117,b.s118,b.s119,b.s120, ")
                .append(" b.s121,b.s122,b.s123,b.s124,b.s125,b.s126,b.s127,b.s128,b.s129,b.s130,b.s131,b.s132,b.s133,b.s134,b.s135,b.s136,b.s137,b.s138,b.s139,b.s140, ")
                .append(" b.s141,b.s142,b.s143,b.s144,b.s145,b.s146,b.s147,b.s148,b.s149,b.s150 ) ");
        super.getJdbcTemplate().batchUpdate(sb.toString(),  new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramList.get(i);
                for(int a = 0; a < 157; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public Double selectSubmitReallyShouldSendSum(String agencyId, String shouldSend) {
            StringBuilder sql = new StringBuilder();
            sql.append(" select ")
                    .append(" nvl(sum(").append(shouldSend).append("), 0.00) reallyShouldSendSum ")
                    .append(" from ")
                    .append(TableName.SALARY_MONTH_BEGIN)
                    .append(" where agency_id = ? ");
            return this.getJdbcTemplate().queryForObject(sql.toString(),Double.class,agencyId);
    }

    @Override
    public String getZeroAble(String province) {
        try{
            StringBuilder sql = new StringBuilder();
            sql.append(" select ")
               .append(" is_able ")
               .append(" from ")
               .append(TableName.SAL_CONFIG_CTRL)
               .append(" where config_type='zeroAble' and  mof_div_code = ? ");
            return this.getJdbcTemplate().queryForObject(sql.toString(),String.class,province);
        }catch (Exception e){
            return "1";
        }
    }
    @Override
    public AgencyVerify getAgencyVerifyBizKey(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append("  biz_key, agency_id, salary_month, is_month_end, is_thirteen");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" where is_month_end = ? and agency_id = ?")
                .append(" and is_thirteen=0 ");
        Object[] objects = new Object[]{
                SystemDictConstant.IS_FALSE, agencyId
        };
        try{
            return this.getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(AgencyVerify.class), objects);
        }catch(EmptyResultDataAccessException e){
            return new AgencyVerify();
        }
    }
}

